﻿#include "mysql.h"

#include <QDir>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

MySql::MySql(QString& Dir,QString &cName)
    : dbDir(Dir),
      connectionName(cName)
{
//    connectionName = "sqlslite";
    if (!isDirExist())        //若路径不存在则创建该路径//
    {
        QDir backup;
        backup.mkdir(dbDir);
    }

    if (!CreateConnection())
    {
        qDebug() << "can not open db!";
        return;
    }
}

MySql::~MySql()
{
    qDebug()<<"~MySql()";

    closeDb();
    QSqlDatabase::removeDatabase(connectionName);

}
MySql * MySql::m_instance = NULL;
MySql *MySql::instance(QString& Dir,QString &cName)
{
    if (NULL == m_instance)
    {
        m_instance = new MySql(Dir,cName);
    }

    return m_instance;
}

MySql *MySql::getInstance(QString& Dir,QString &cName)
{
    return instance(Dir,cName);
}

bool MySql::CreateConnection()
{
    db = QSqlDatabase::addDatabase("QSQLITE",connectionName);  //创建一个SQLite数据库连接//
    QString dbName = dbDir + "js.db";                         //本地数据库文件名//
    db.setDatabaseName(dbName);                                //数据库连接命名//
    if (!db.open())                                            //如果.db文件不存在，自动新建.db文件并打开//
    {
        return false;
    }

    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.exec("create table UserInfo(ID int primary key,UserID varchar(50)  NOT NULL,UserName varchar(50),UserPwd varchar(50),UserAge varchar(8),UserSex varchar(8),IsLogin varchar(8),CurrentIP varchar(20))");   //新建一张表，访问已有.bd时，执行该语句也不受影响//

    query.exec("create table DataInfo(ID int primary key,TaskID varchar(50)  NOT NULL,MeasureTime varchar(20),TaskType varchar(10),Theme varchar(20),Content varchar(20),TaskResult varchar(5000))");
    return true;
}


bool MySql::isDirExist()
{
    QDir temp_dir(dbDir);
    if (temp_dir.exists())
    {
        return true;
    }
    else
    {
        return false;
    }
}

void MySql::closeDb()
{
    if (db.isOpen())
    {
        db.close();
    }
}

bool MySql::MyInsert(UserInfoBean& userInfo)
{
    requestAccessToken();

    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("insert into UserInfo(UserID,UserName,UserPwd,UserSex,UserAge,IsLogin,CurrentIP)"
        "values(:UserID,:UserName,:UserPwd,:UserSex,:UserAge,:IsLogin,:CurrentIP)");
    query.bindValue(":UserID",userInfo.UserID);
    query.bindValue(":UserName",userInfo.UserName);
    query.bindValue(":UserPwd",userInfo.UserPwd);
    query.bindValue(":UserSex",userInfo.UserSex);
    query.bindValue(":UserAge",userInfo.UserAge);
    query.bindValue(":IsLogin",userInfo.IsLogin);
    query.bindValue(":CurrentIP",userInfo.CurrentIP);
    if (!query.exec())
    {
        qDebug() << "/**********MyInsert***/"<< query.lastError();
        releaseAccessToken();
        return false;
    }

    releaseAccessToken();
    return true;


}
bool MySql::MyDataInsert(DataInfoBean& dataInfo)
{
    requestAccessToken();

    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("insert into DataInfo(TaskID,MeasureTime,TaskType,Theme,Content,TaskResult)"
                  "values(:TaskID,:MeasureTime,:TaskType,:Theme,:Content,:TaskResult)");
    query.bindValue(":TaskID",dataInfo.TaskID);
    query.bindValue(":MeasureTime",dataInfo.MeasureTime);
    query.bindValue(":TaskType",dataInfo.TaskType);
    query.bindValue(":Theme",dataInfo.Theme);
    query.bindValue(":Content",dataInfo.Content);
    query.bindValue(":TaskResult",dataInfo.TaskResult);
    if (!query.exec())
    {
        releaseAccessToken();
        return false;
    }

    releaseAccessToken();
    return true;

}
bool MySql::MySelect(QString userID,UserInfoBean& userInfo)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from UserInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);
    if (!query.exec())
    {
        qDebug()<<"query:MySelect"<<query.lastError();
        releaseAccessToken();
        return false;
    }
    query.next();
    userInfo.UserID = query.value(1).toString();
    userInfo.UserName = query.value(2).toString();
    userInfo.UserPwd = query.value(3).toString();
    userInfo.UserAge = query.value(4).toString();
    userInfo.UserSex = query.value(5).toString();
    userInfo.IsLogin = query.value(6).toString();
    userInfo.CurrentIP=query.value(7).toString();
    releaseAccessToken();

    return true;
}
bool MySql::Query(QString userID)
{
    requestAccessToken();
    bool b=false;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from UserInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);
    if (query.exec())
    {
        if(query.next())
            b=true;
    }
    releaseAccessToken();
    return b;
}
bool MySql::MyDelete(QString userID)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("delete from UserInfo where UserID = :UserID");
    query.bindValue(":UserID",userID);

    if (!query.exec())
    {
        releaseAccessToken();
        return false;
    }

    releaseAccessToken();
    return true;
}

bool MySql::MyUpdate(UserInfoBean& userInfo)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    query.prepare("update  UserInfo set UserName=:UserName,UserPwd=:UserPwd ,UserAge=:UserAge,UserSex=:UserSex,IsLogin=:IsLogin ,CurrentIP=:CurrentIP where UserID=:UserID");
    query.bindValue(":UserID",userInfo.UserID);
    query.bindValue(":UserName",userInfo.UserName);
    query.bindValue(":UserAge",userInfo.UserAge);
    query.bindValue(":UserSex",userInfo.UserSex);
    query.bindValue(":UserPwd",userInfo.UserPwd);
    query.bindValue(":IsLogin",userInfo.IsLogin);
    query.bindValue(":CurrentIP",userInfo.CurrentIP);
    if (!query.exec())
    {
        releaseAccessToken();
        return false;
    }else
    {
    releaseAccessToken();
    return true;
    }
}
bool MySql::MyUpdateLoginState(QString ip,QString LoginState)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    query.prepare("update  UserInfo set IsLogin=:IsLogin  where CurrentIP=:CurrentIP");
    query.bindValue(":IsLogin",LoginState);
    query.bindValue(":CurrentIP",ip);
    if (!query.exec())
    {
    releaseAccessToken();
    return false;
    }else
    {
    releaseAccessToken();
    return true;
    }
}
bool MySql::MyUpdateLoginState_All()
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    query.prepare("update  UserInfo set IsLogin=:newLogin  where IsLogin=:oldLogin");
    query.bindValue(":newLogin","0");
    query.bindValue(":oldLogin","1");
    if (!query.exec())
    {
    releaseAccessToken();
    return false;
    }else
    {
    releaseAccessToken();
    return true;
    }
}
void  MySql::requestAccessToken(void)
{
    mutex.lock();                           //如果已经lock且还未unlock，一直等待，直到unlock，然后lock//
}

void  MySql::releaseAccessToken(void)
{
    mutex.unlock();
}
QList<UserInfoBean> MySql::QueryAll()
{
    requestAccessToken();
    QList<UserInfoBean>uibs;
    UserInfoBean userInfo;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from UserInfo");
    if(query.exec()){
        while(query.next()){
            userInfo.UserID = query.value(1).toString();
            userInfo.UserName = query.value(2).toString();
            userInfo.UserPwd = query.value(3).toString();
            userInfo.UserAge = query.value(4).toString();
            userInfo.UserSex = query.value(5).toString();
            userInfo.IsLogin = query.value(6).toString();
            userInfo.CurrentIP=query.value(7).toString();
            uibs.append(userInfo);
        }
    }
    releaseAccessToken();
    return uibs;


}
//查询测量结果数据
QList<DataInfoBean> MySql::DataQueryAll()
{
    requestAccessToken();
    QList<DataInfoBean>dibs;
    DataInfoBean dataInfo;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("select * from DataInfo");
    if(query.exec()){
        while(query.next()){
            dataInfo.TaskID = query.value(1).toString();
            dataInfo.TaskType = query.value(3).toString();
            dataInfo.Theme=query.value(4).toString();
            dataInfo.Content=query.value(5).toString();
            dataInfo.TaskResult = query.value(6).toString();
            dataInfo.MeasureTime = query.value(2).toString();
            dibs.append(dataInfo);
        }
    }
    releaseAccessToken();
    return dibs;
}
bool MySql::MyDataDelete(QString Measuretime)
{
    requestAccessToken();
    QSqlQuery query(QSqlDatabase::database(connectionName));
    query.prepare("delete from DataInfo where MeasureTime = :MeasureTime");
    query.bindValue(":MeasureTime",Measuretime);

    if (!query.exec())
    {
        releaseAccessToken();
        return false;
    }

    releaseAccessToken();
    return true;
}
QList<DataInfoBean> MySql::DataQueryBy(QString ID,QString MeasureTime)
{
    requestAccessToken();
    bool b=false;
    QList<DataInfoBean>dibs;
    DataInfoBean dataInfo;
    QSqlQuery query(QSqlDatabase::database(connectionName));
    if(ID.size()>0 && MeasureTime.size()>2)
    {
        QString dd=QString("select * from DataInfo where MeasureTime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(MeasureTime,MeasureTime);
        query.prepare(dd+" AND TaskID=:TaskID");
        query.bindValue(":TaskID",ID);
    }else if(ID.size()>0 && MeasureTime.size()<=2)
    {
        query.prepare("select * from DataInfo where TaskID=:TaskID");
        query.bindValue(":TaskID",ID);
    }else if(ID.size()==0 && MeasureTime.size()>2)
    {
        QString dd=QString("select * from DataInfo where MeasureTime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(MeasureTime,MeasureTime);
        query.prepare(dd);
    }else{
        query.prepare("select * from DataInfo");
    }

    if(query.exec()){
        while(query.next()){
            dataInfo.TaskID = query.value(1).toString();
            dataInfo.TaskType = query.value(3).toString();
            dataInfo.Theme=query.value(4).toString();
            dataInfo.Content=query.value(5).toString();
            dataInfo.TaskResult = query.value(6).toString();
            dataInfo.MeasureTime = query.value(2).toString();
            dibs.append(dataInfo);
        }
    }
    releaseAccessToken();
    return dibs;
}
